--exec sp_shipped_month_year_report 1
ALTER              proc sp_shipped_month_year_report
@order_type int 
as
begin


	declare @results Table(ProductID int, COOlQty int, COOLUnitCost float, COOLReqDate datetime, COOLSchDate datetime,
				PartNo varchar(50), CSLITQtyShipped int, CSLITShippedDate datetime, SoNo varchar(50), CustId int, SoPoNo varchar(30),
				SoPerson varchar(50), SoBkDate datetime, RegRep varchar(20), CustName varchar(65),
				CustNo varchar(20), CSTstatus varchar(15), CSTShipDate datetime, InvNo varchar(20), IsRMALineItem int, IsRMACredit VARCHAR(3), COOlLineItemNo int)
	
	declare @productID int, @cOOlQty int
	declare @cOOLUnitCost float
	declare @cOOLReqDate datetime, @cOOLSchDate datetime
	declare @partNo varchar(50)
	declare @cSLITQtyShipped int
	declare @cSLITShippedDate datetime
	declare @soNo varchar(50)
	declare @cust_id int
	declare @soPoNo varchar(30),@soPerson varchar(50)
	declare @soBkDate datetime
	declare @regRep varchar(20), @custName varchar(65),@custNo varchar(20), @cSTstatus varchar(15)
	declare @cSTShipDate datetime
	declare @invNo varchar(20)
	declare	@isRMALineItem int
	declare @isdistributor	int

	DECLARE @isRMACredit VARCHAR(3)
	declare @cOOLLineItemNo int

	SET @isRMALineItem = 2
	
	-- FOR NON-RMA LINE ITEMS BY DEFAULT 'NO' IS INSERTED AS isRMACredit. FOR NON-RMA LINE ITEMS, THIS FIELD NEED NOT BE USED.
	SET @isRMACredit = 'NO'

	declare mainCursor cursor for
	SELECT
		Customer_Open_Order_LineItem.Product_ID, 
		Customer_Open_Order_LineItem.COOL_Qty, 
		Customer_Open_Order_LineItem.COOL_Unit_Cost, 
		Customer_Open_Order_LineItem.COOL_Requested_Date, 
		Customer_Open_Order_LineItem.COOL_Schedule_Date,
		Product.Part_Number,
		Customer_Shipping_Line_Item_Table.CSLIT_Qty_Shipped, 
		Customer_Shipping_Line_Item_Table.CSLIT_Shipped_Date,
		Sales_Order.SalesOrder_No, 
		Sales_Order.Customer_ID, 
		Sales_Order.SO_Customer_PO_Number, 
		Sales_Order.SO_Sales_Person, 
		Sales_Order.SO_Booking_Date, 
		Sales_Order.Regional_Rep,
		Customer.C_Name, Customer.C_Number,Customer.C_Is_Distributor,
		Customer_Shipping_Table.CST_Status, 
		Customer_Shipping_Table.CST_Ship_Date, 
		Customer_Shipping_Table.Inv_No,
		Customer_Open_Order_LineItem.COOL_Line_Item_No		
	FROM
	    ((((Customer_Open_Order_LineItem INNER JOIN Customer_Shipping_Line_Item_Table ON
	        Customer_Open_Order_LineItem.LineItem_ID = Customer_Shipping_Line_Item_Table.LineItem_ID
	     INNER JOIN Sales_Order ON
	        Customer_Open_Order_LineItem.SalesOrder_ID = Sales_Order.SalesOrder_ID and Sales_Order.SO_Is_Sample = @order_type)
	     INNER JOIN Product ON
	        Customer_Open_Order_LineItem.Product_ID = Product.Product_ID)
	     INNER JOIN Customer_Shipping_Table ON
	        Customer_Shipping_Line_Item_Table.CustomerShipping_ID = Customer_Shipping_Table.CustomerShipping_ID)
	     INNER JOIN Customer ON
	        Sales_Order.Customer_ID = Customer.Customer_ID)
	WHERE
	    Customer_Shipping_Table.CST_Status = 'SHIPPED'	


		open mainCursor
		fetch next from mainCursor into 	
		@productID, @cOOlQty, @cOOLUnitCost, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
		@soNo, @cust_id, @soPoNo, @soPerson, @soBkDate, @regRep, @custName, @custNo,@isdistributor, @cSTstatus, @cSTShipDate, @invNo, @cOOLLineItemNo 
		while @@fetch_status = 0
		begin
			if ( @isdistributor is null)
			begin
				set @isRMALineItem = 2
			end
			else if ( @isdistributor = 1 )
			begin
				set @isRMALineItem = 1
			end
			
			insert into @results values(@productID, @cOOlQty,@cOOLUnitCost,@cOOLReqDate, @cOOLSchDate,@partNo,@cSLITQtyShipped,@cSLITShippedDate, 
			@soNo,@cust_id, @soPoNo,@soPerson,@soBkDate,@regRep, @custName,@custNo, @cSTstatus,@cSTShipDate,@invNo, @isRMALineItem, @isRmaCredit, @cOOLLineItemNo )
	
			set @productID=null
			set @cOOlQty=null
			set @cOOLUnitCost=null
			set @cOOLReqDate=null
			set @cOOLSchDate=null
			set @partNo=null
			set @cSLITQtyShipped=null
			set @cSLITShippedDate=null 
			set @soNo=null
			set @cust_id=null
			set @soPoNo=null
			set @soPerson=null
			set @soBkDate=null
			set @regRep=null
			set @custName=null
			set @custNo=null
			set @cSTstatus=null
			set @cSTShipDate=null
			set @invNo=null
			set @cOOLLineItemNo=null
			set @isdistributor = null

			fetch next from mainCursor into 	
			@productID, @cOOlQty,@cOOLUnitCost,@cOOLReqDate, @cOOLSchDate,@partNo,@cSLITQtyShipped,@cSLITShippedDate, 
			@soNo,@cust_id, @soPoNo,@soPerson,@soBkDate,@regRep, @custName,@custNo,@isdistributor,@cSTstatus, @cSTShipDate, @invNo, @cOOLLineItemNo
		end
		close mainCursor
		deallocate mainCursor

		DECLARE @RMA_ID INT
		DECLARE @IS_CREDIT INT	

		declare @lineItemCloseDate datetime
		declare @lineItemStatus varchar(10)

		--DECLARE @TEMPCOST INT
		--SET @TEMPCOST = 0
		
		SET @isRMALineItem = 0
		
		-- PRABHANJAN (27 MAY 2004)
		SET @IS_CREDIT = 1

		if( @order_type <> 0)
		begin
		
		-- Modified by Prabhanjan(11 May 2004) to retreive Close_Date as @cSTShipDate
		DECLARE rmaCursor CURSOR FOR
		SELECT RMA_TABLE.RMA_ID, RMA_Number, Customer_ID, Customer_Name, RMA_Requested_Date, RMA_Table.Close_Date, Part_Number, Quantity, Unit_Price, PO_Number, Is_Credit, LineItem_Number , RMA_LINE_ITEM_TABLE.Close_Date , RMA_LINE_ITEM_TABLE.Status, RMA_LINE_ITEM_TABLE.Invoice_Number  FROM RMA_TABLE, RMA_LINE_ITEM_TABLE WHERE RMA_TABLE.RMA_ID = RMA_LINE_ITEM_TABLE.RMA_ID AND RMA_LINE_ITEM_TABLE.Status <> 'CANCELLED'
				
		OPEN rmaCursor 
		FETCH NEXT FROM rmaCursor INTO @RMA_ID, @soNo, @cust_id, @custName, @cOOLReqDate, @cSTShipDate, @partNo, @cOOlQty, @cOOLUnitCost, @soPoNo, @IS_CREDIT, @cOOLLineItemNo , @lineItemCloseDate , @lineItemStatus, @InvNo
		WHILE @@FETCH_STATUS = 0
		BEGIN
			if (@lineItemStatus = 'CLOSE' )
			begin
				set @cSTShipDate = @lineItemCloseDate
			end


			SET @cSLITQtyShipped = @cOOLQty
			-- SET @cooLUnitCost =  @TEMPCOST - @cOOLUnitCost
			-- Commented by Prabhanjan (11 May 2004) - Now @cSTShipDate is taken as Close_Date of RMA, not Requested Date.
			-- SET @cSTShipDate = @cOOLReqDate

			SELECT @custNo = C_NUMBER FROM CUSTOMER WHERE CUSTOMER_ID = @cust_Id
			
			select @productID = Product.Product_ID from product,Process_Step where Part_Number = @partNo and product.processStep_id=Process_Step.ProcessStep_ID and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name = 'Finished Goods')
			
			-- ADDED BY PRABHANJAN (27 May 2004).TO INDICATE WHETHER CREDIT IS SELECTED OR NOT.
			IF (@IS_CREDIT	= 1)
			BEGIN
				SET @isRMACredit = 'NO'
			END
			ELSE IF (@IS_CREDIT = 0)
			BEGIN
				SET @isRMACredit = 'YES'
			END				

			-- Modified by Prabhanjan on (11 May 2004) to insert only those RMA which has Close Date available.
			IF @cSTShipDate is not null
			BEGIN
				INSERT INTO @results VALUES (@productID, @cOOlQty, @cOOLUnitCost, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
				@soNo, @cust_id, @soPoNo, @soPerson, @soBkDate, @regRep, @custName, @custNo, @cSTstatus, @cSTShipDate, @invNo, @isRMALineItem, @isRMACredit, @cOOLLineItemNo )
			END
			
			--INSERT INTO @results VALUES(NULL, @cOOlQty, @cOOLUnitCost, @cOOLReqDate, NULL, @partNo, @cSLITQtyShipped, NULL, 
			--@soNo, @cust_id, @soPoNo, NULL, NULL, NULL, @custName, @custNo, NULL, @cSTShipDate, NULL, @isRMALineItem)
		
			set @productID=null
			set @cOOlQty=null
			set @cOOLUnitCost=null
			set @cOOLReqDate=null
			set @cOOLSchDate=null
			set @partNo=null
			set @cSLITQtyShipped=null
			set @cSLITShippedDate=null 
			set @soNo=null
			set @cust_id=null
			set @soPoNo=null
			set @soPerson=null
			set @soBkDate=null
			set @regRep=null
			set @custName=null
			set @custNo=null
			set @cSTstatus=null
			set @cSTShipDate=null
			set @invNo=null

			SET @IS_CREDIT = 1
			set @cOOLLineItemNo = null

			set @lineItemCloseDate = null
			set @lineItemStatus =  null
					

			FETCH NEXT FROM rmaCursor INTO @RMA_ID, @soNo, @cust_id, @custName, @cOOLReqDate, @cSTShipDate, @partNo, @cOOlQty, @cOOLUnitCost, @soPoNo, @IS_CREDIT, @cOOLLineItemNo , @lineItemCloseDate , @lineItemStatus, @InvNo

		END
		CLOSE rmaCursor
		DEALLOCATE rmaCursor
		end	
		-- To Show Distributor Ship Info. @isRMALineItem = 2 Customer Ship,=1 Distributoor Ship,=0 rma
		INSERT INTO @RESULTS
				(SoNo,SoPoNo,CustId,CustName,SoPerson,SoBkDate,COOlLineItemNo,PartNo,ProductID,COOlQty,COOLReqDate,
				 CSLITQtyShipped,CSTShipDate,COOLUnitCost,IsRMALineItem,InvNo)
		SELECT 
			 DSO.DIST_SALESORDER_NO,DSO.CUSTOMER_PO,DSO.CUSTOMER_ID,
			(SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = DSO.CUSTOMER_ID ) AS CUSTOMER_NAME,
			(SELECT C_SALES_PERSON FROM CUSTOMER WHERE CUSTOMER_ID = DSO.CUSTOMER_ID ) AS SALES_PERSON,
			DSO.SALESORDER_DATE,DLIT.DIST_LINEITEM_NO, 
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = DLIT.PRODUCT_ID) AS PRODUCT,DLIT.PRODUCT_ID,
			DLIT.DIST_REQ_QTY,DLIT.DIST_REQ_DATE,DLIT.DIST_SHIP_QTY,DLIT.DIST_SHIP_DATE,DLIT.UNIT_COST,2,''
		FROM 
			 (DIST_SALES_ORDER DSO INNER JOIN DIST_LINE_ITEM_TABLE DLIT ON
		          DSO.DIST_SALESORDER_ID = DLIT.DIST_SALESORDER_ID)
		
	select * from @results
	
end